<!DOCTYPE html>





<html class="theme-next muse use-motion" lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="generator" content="Hexo 3.9.0">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png?v=7.3.0">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png?v=7.3.0">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png?v=7.3.0">
  <link rel="mask-icon" href="/images/logo.svg?v=7.3.0" color="#222">

<link rel="stylesheet" href="/css/main.css?v=7.3.0">


<link rel="stylesheet" href="/lib/font-awesome/css/font-awesome.min.css?v=4.7.0">


<script id="hexo-configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/',
    scheme: 'Muse',
    version: '7.3.0',
    exturl: false,
    sidebar: {"position":"left","display":"post","offset":12,"onmobile":false},
    back2top: {"enable":true,"sidebar":false,"scrollpercent":false},
    save_scroll: false,
    copycode: {"enable":false,"show_result":false,"style":null},
    fancybox: false,
    mediumzoom: false,
    lazyload: false,
    pangu: false,
    algolia: {
      appID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":10},
      labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
    },
    localsearch: {"enable":false,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false},
    path: '',
    motion: {"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},
    translation: {
      copy_button: '复制',
      copy_success: '复制成功',
      copy_failure: '复制失败'
    }
  };
</script>

  <meta name="description" content="数据库连接1mysql –u用户名 [–h主机名或者IP地址,-P端口号] –p密码  命令输入在mysql&amp;gt; 之后:  用q\、quit、exit三种命令可以退出命令行实用程序；">
<meta name="keywords" content="MySQL数据库">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL数据库">
<meta property="og:url" content="http://yoursite.com/2019/06/25/MySQL数据库/index.html">
<meta property="og:site_name" content="XuQi&#39;s Blog">
<meta property="og:description" content="数据库连接1mysql –u用户名 [–h主机名或者IP地址,-P端口号] –p密码  命令输入在mysql&amp;gt; 之后:  用q\、quit、exit三种命令可以退出命令行实用程序；">
<meta property="og:locale" content="zh-CN">
<meta property="og:updated_time" content="2019-10-20T13:23:23.041Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="MySQL数据库">
<meta name="twitter:description" content="数据库连接1mysql –u用户名 [–h主机名或者IP地址,-P端口号] –p密码  命令输入在mysql&amp;gt; 之后:  用q\、quit、exit三种命令可以退出命令行实用程序；">
  <link rel="canonical" href="http://yoursite.com/2019/06/25/MySQL数据库/">


<script id="page-configurations">
  // https://hexo.io/docs/variables.html
  CONFIG.page = {
    sidebar: "",
    isHome: false,
    isPost: true,
    isPage: false,
    isArchive: false
  };
</script>

  <title>MySQL数据库 | XuQi's Blog</title>
  








  <noscript>
  <style>
  .use-motion .motion-element,
  .use-motion .brand,
  .use-motion .menu-item,
  .sidebar-inner,
  .use-motion .post-block,
  .use-motion .pagination,
  .use-motion .comments,
  .use-motion .post-header,
  .use-motion .post-body,
  .use-motion .collection-title { opacity: initial; }

  .use-motion .logo,
  .use-motion .site-title,
  .use-motion .site-subtitle {
    opacity: initial;
    top: initial;
  }

  .use-motion .logo-line-before i { left: initial; }
  .use-motion .logo-line-after i { right: initial; }
  </style>
</noscript>

</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-CN">

  <div class="container sidebar-position-left">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-wrapper">
  <div class="site-meta">

    <div class="custom-logo-site-title">
      <a href="/" class="brand" rel="start">
        <span class="logo-line-before"><i></i></span>
        <span class="site-title">XuQi's Blog</span>
        <span class="logo-line-after"><i></i></span>
      </a>
    </div>
  </div>

  <div class="site-nav-toggle">
    <button aria-label="切换导航栏">
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
    </button>
  </div>
</div>


<nav class="site-nav">
  
  <ul id="menu" class="menu">
      
      
      
        
        <li class="menu-item menu-item-home">
      
    

    <a href="/" rel="section"><i class="menu-item-icon fa fa-fw fa-home"></i> <br>首页</a>

  </li>
      
      
      
        
        <li class="menu-item menu-item-archives">
      
    

    <a href="/archives/" rel="section"><i class="menu-item-icon fa fa-fw fa-archive"></i> <br>归档</a>

  </li>
  </ul>

    

</nav>
</div>
    </header>

    


    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content page-post-detail">
            

  <div id="posts" class="posts-expand">
    

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="http://yoursite.com/2019/06/25/MySQL数据库/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="XuQi">
      <meta itemprop="description" content="">
      <meta itemprop="image" content="/images/avatar.gif">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="XuQi's Blog">
    </span>
      <header class="post-header">

        
          <h1 class="post-title" itemprop="name headline">MySQL数据库

            
          </h1>
        

        <div class="post-meta">
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              <span class="post-meta-item-text">发表于</span>

              
                
              

              <time title="创建时间：2019-06-25 22:00:28" itemprop="dateCreated datePublished" datetime="2019-06-25T22:00:28+00:00">2019-06-25</time>
            </span>
          
            

            
              <span class="post-meta-item">
                <span class="post-meta-item-icon">
                  <i class="fa fa-calendar-check-o"></i>
                </span>
                <span class="post-meta-item-text">更新于</span>
                <time title="修改时间：2019-10-20 13:23:23" itemprop="dateModified" datetime="2019-10-20T13:23:23+00:00">2019-10-20</time>
              </span>
            
          

          

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
        <h3 id="数据库连接"><a href="#数据库连接" class="headerlink" title="数据库连接"></a>数据库连接</h3><figure class="highlight sh"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql –u用户名 [–h主机名或者IP地址,-P端口号] –p密码</span><br></pre></td></tr></table></figure>

<p>命令输入在<code>mysql&gt;</code> 之后:</p>
<ul>
<li>用<code>q\</code>、<code>quit</code>、<code>exit</code>三种命令可以退出命令行实用程序；</li>
</ul>
<a id="more"></a>

<h4 id="开启MySQL的远程帐号"><a href="#开启MySQL的远程帐号" class="headerlink" title="开启MySQL的远程帐号"></a>开启MySQL的远程帐号</h4><p>用GUI连接数据库如果报下面错误，是你的 MySQL远程连接账号没有开启。</p>
<figure class="highlight sh"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">Unable to connect to host 192.168.188.114, or the request timed out.</span><br><span class="line">Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).</span><br><span class="line">MySQL said: Host <span class="string">'192.168.188.106'</span> is not allowed to connect to this MariaDB server</span><br></pre></td></tr></table></figure>

<p>通过下面的命令，解决不能连接的错误，进入 MySQL 执行下面语句。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"># 你想root使用123456从&apos;192.168.188.106&apos;主机连接到mysql服务器 wabg库下面所有表的话。</span><br><span class="line">MySQL&gt; grant all PRIVILEGES on wabg.* to  root@&apos;192.168.188.106&apos;  identified by &apos;123456&apos; WITH GRANT OPTION;</span><br><span class="line"># 你想myuser使用mypassword从任何主机连接到mysql服务器的话</span><br><span class="line">MySQL&gt; grant all PRIVILEGES on *.* to  &apos;myuser&apos;@&apos;%&apos;  identified by &apos;mypassword&apos; WITH GRANT OPTION;</span><br></pre></td></tr></table></figure>

<p>上面的语句表示将 wabg 数据库的所有权限授权给 root 这个用户，允许 root 用户在 192.168.1.106 这个 IP 进行远程登陆，并设置 root 用户的密码为 123456 。</p>
<ul>
<li><p>all PRIVILEGES 表示赋予所有的权限给指定用户，这里也可以替换为赋予某一具体的权限</p>
</li>
<li><p>wabg.* 表示上面的权限是针对于哪个表的，wabg指的是数据库，后面的 * 表示对于所有的表，由此可以推理出：</p>
<ul>
<li>对于全部数据库的全部表授权为“ <em>.</em> ”</li>
<li>对于某一数据库的全部表授权为“ 数据库名.* ”</li>
<li>对于某一数据库的某一表授权为“数据库名.表名”</li>
</ul>
</li>
<li><p>root 表示你要给哪个用户授权，这个用户可以是存在的用户，也可以是不存在的用户。</p>
</li>
<li><p>192.168.188.106 表示允许远程连接的 IP 地址，如果想不限制链接的 IP 则设置为“%”即可。</p>
</li>
<li><p>123456 为用户的密码。</p>
<p>如何开启MySQL的远程帐号？执行了上面的语句后，再执行下面的语句，方可立即生效。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">MySQL&gt; flush privileges;</span><br></pre></td></tr></table></figure>

</li>
</ul>
<p>当你报下面错误，提示您的密码不满足当前的策略要求。错误如下：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">ERROR 1819 (HY000): Your password does not satisfy the current policy requirements</span><br><span class="line"># 或者</span><br><span class="line">mysqladmin: unable to change password; error: &apos;Your password does not satisfy the current policy requirements&apos;</span><br></pre></td></tr></table></figure>

<p>解决方法：可以按照现有策略设置密码，也可以更改密码策略。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"># 更改密码策略为LOW</span><br><span class="line">MySQL&gt; set global validate_password_policy=0;</span><br><span class="line"># 更改密码长度 密码最小长度为4</span><br><span class="line">MySQL&gt; set global validate_password_length=4;</span><br></pre></td></tr></table></figure>

<p>进入 MySQL 查看你的密码验证策略</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SHOW VARIABLES LIKE &apos;validate_password%&apos;;</span><br><span class="line">+--------------------------------------+-------+</span><br><span class="line">| Variable_name                        | Value |</span><br><span class="line">+--------------------------------------+-------+</span><br><span class="line">| validate_password_check_user_name    | OFF   | </span><br><span class="line">| validate_password_dictionary_file    |       | </span><br><span class="line">| validate_password_length             | 4     | </span><br><span class="line">| validate_password_mixed_case_count   | 1     | </span><br><span class="line">| validate_password_number_count       | 1     | </span><br><span class="line">| validate_password_policy             | LOW   | </span><br><span class="line">| validate_password_special_char_count | 1     | </span><br><span class="line">+--------------------------------------+-------+</span><br><span class="line">7 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>

<ul>
<li>validate_password_check_user_name 不得使用当前会话用户名作为密码的一部分</li>
<li>validate_password_dictionary_file 验证密码强度的字典文件路径</li>
<li>validate_password_length 密码最小长度</li>
<li>validate_password_mixed_case_count 密码至少要包含的小写字母个数和大写字母个数</li>
<li>validate_password_number_count 密码至少要包含的数字个数</li>
<li>validate_password_policy 密码强度检查等级，0/LOW、1/MEDIUM、2/STRONG<ul>
<li>0/LOW：只检查长度。</li>
<li>1/MEDIUM：检查长度、数字、大小写、特殊字符。</li>
<li>2/STRONG：检查长度、数字、大小写、特殊字符字典文件。</li>
</ul>
</li>
<li>validate_password_special_char_count 密码至少要包含的特殊字符数</li>
</ul>
<h4 id="MySQL修改密码"><a href="#MySQL修改密码" class="headerlink" title="MySQL修改密码"></a>MySQL修改密码</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqladmin -uroot -p password</span><br></pre></td></tr></table></figure>

<h3 id="创建数据库"><a href="#创建数据库" class="headerlink" title="创建数据库"></a>创建数据库</h3><p>对于表的操作需要先进入库<code>use 库名;</code></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">-- 创建一个名为 samp_db 的数据库，数据库字符编码指定为 gbk</span><br><span class="line">create database samp_db character set gbk;</span><br><span class="line">drop database samp_db; -- 删除 库名为samp_db的库</span><br><span class="line">show databases;        -- 显示数据库列表。</span><br><span class="line">use samp_db;     -- 选择创建的数据库samp_db</span><br><span class="line">show tables;     -- 显示samp_db下面所有的表名字</span><br><span class="line">describe 表名;    -- 显示数据表的结构</span><br><span class="line">delete from 表名; -- 清空表中记录</span><br></pre></td></tr></table></figure>

<h3 id="创建数据库表"><a href="#创建数据库表" class="headerlink" title="创建数据库表"></a>创建数据库表</h3><blockquote>
<p>使用 create table 语句可完成对表的创建, create table 的常见形式: 语法：create table 表名称(列声明);</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">-- 如果数据库中存在user_accounts表，就把它从数据库中drop掉</span><br><span class="line">DROP TABLE IF EXISTS `user_accounts`;</span><br><span class="line">CREATE TABLE `user_accounts` (</span><br><span class="line">  `id`             int(100) unsigned NOT NULL AUTO_INCREMENT primary key,</span><br><span class="line">  `password`       varchar(32)       NOT NULL DEFAULT &apos;&apos; COMMENT &apos;用户密码&apos;,</span><br><span class="line">  `reset_password` tinyint(32)       NOT NULL DEFAULT 0 COMMENT &apos;用户类型：0－不需要重置密码；1-需要重置密码&apos;,</span><br><span class="line">  `mobile`         varchar(20)       NOT NULL DEFAULT &apos;&apos; COMMENT &apos;手机&apos;,</span><br><span class="line">  `create_at`      timestamp(6)      NOT NULL DEFAULT CURRENT_TIMESTAMP(6),</span><br><span class="line">  `update_at`      timestamp(6)      NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),</span><br><span class="line">  -- 创建唯一索引，不允许重复</span><br><span class="line">  UNIQUE INDEX idx_user_mobile(`mobile`)</span><br><span class="line">)</span><br><span class="line">ENGINE=InnoDB DEFAULT CHARSET=utf8</span><br><span class="line">COMMENT=&apos;用户表信息&apos;;</span><br></pre></td></tr></table></figure>

<p>数据类型的属性解释</p>
<ul>
<li><p><code>NULL</code>：数据列可包含NULL值；</p>
</li>
<li><p><code>NOT NULL</code>：数据列不允许包含NULL值；</p>
</li>
<li><p><code>DEFAULT</code>：默认值；</p>
</li>
<li><p><code>PRIMARY KEY</code>：主键；</p>
</li>
<li><p><code>AUTO_INCREMENT</code>：自动递增，适用于整数类型；</p>
</li>
<li><p><code>UNSIGNED</code>：是指数值类型只能为正数；</p>
</li>
<li><p><code>CHARACTER SET name</code>：指定一个字符集；</p>
</li>
<li><p><code>COMMENT</code>：对表或者字段说明；</p>
</li>
</ul>
<h3 id="检索数据"><a href="#检索数据" class="headerlink" title="检索数据"></a>检索数据</h3><ul>
<li>检索多列</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_id,prod_name FROM products;</span><br></pre></td></tr></table></figure>

<ul>
<li>检索所有列</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM products;</span><br></pre></td></tr></table></figure>

<ul>
<li>检索不同行 (DISTINCT返回不重复的值)</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT DISTINCT vend_id FROM products;</span><br></pre></td></tr></table></figure>

<p>注：</p>
<blockquote>
<p>不能部分使用DISTINCT, DISTINCT关键字应用于所有列，而不是前置列。</p>
<p>SELECT DISTINCT vend_id,prod_price 除非两个列都不同，否则所有行都被检索出来。</p>
</blockquote>
<ul>
<li>限制结果(LIMIT)</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_name FROM products LIMIT 5;</span><br><span class="line">SELECT prod_name FROM products LIMIT 5,5; # 从第5行开始的5行</span><br><span class="line">SELECT prod_name FROM products LIMIT 5 OFFSET 5; # 从第5行开始的5行</span><br></pre></td></tr></table></figure>

<blockquote>
<p>行0 LIMIT 1，1 检索出来的事第二行而不是第一行</p>
</blockquote>
<ul>
<li>使用完全限定的表名</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT products.prod_name FROM products;</span><br></pre></td></tr></table></figure>

<h4 id="排序检索数据"><a href="#排序检索数据" class="headerlink" title="排序检索数据"></a>排序检索数据</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_name FROM products ORDER BY prod_name;</span><br></pre></td></tr></table></figure>

<ul>
<li>按多个列排序</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 首先按照价格，再按照名称</span><br><span class="line">SELECT prod_id,prod_name FROM products ORDER BY prod_price, prod_name;</span><br></pre></td></tr></table></figure>

<ul>
<li>指定方向排序</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 按照价格降序排序</span><br><span class="line">SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC;</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#按照价格降序，再按照名称默认升序排列，如果需要名称也降序，需要prod_name后面也指定DESC</span><br><span class="line">SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC,prod_name;</span><br></pre></td></tr></table></figure>

<p><strong>ORDER BY子句的位置应该在FROM之后，如果使用LIMIT 他必须在ORDER BY之后</strong></p>
<h3 id="过滤数据"><a href="#过滤数据" class="headerlink" title="过滤数据"></a>过滤数据</h3><h4 id="使用WHERE子句"><a href="#使用WHERE子句" class="headerlink" title="使用WHERE子句"></a>使用WHERE子句</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_name </span><br><span class="line">FROM products </span><br><span class="line">WHERE prod_price = 2.50;</span><br></pre></td></tr></table></figure>

<ul>
<li>范围值检查</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_name,prod_price </span><br><span class="line">FROM products </span><br><span class="line">WHERE prod_price BETWEEN 5 AND 10;</span><br></pre></td></tr></table></figure>

<ul>
<li>空值检查</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT cust_id </span><br><span class="line">FROM customers </span><br><span class="line">WHERE cust_email IS NULL;</span><br></pre></td></tr></table></figure>

<ul>
<li>组合WHERE 子句 AND/OR</li>
</ul>
<h5 id="计算次序"><a href="#计算次序" class="headerlink" title="计算次序"></a>计算次序</h5><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_name, prod_price </span><br><span class="line">FROM products </span><br><span class="line">WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price &gt;= 10;</span><br><span class="line"># AND优先级比OR高，所以vend_id = 1002 OR (vend_id = 1003 AND prod_price &gt;= 10);</span><br></pre></td></tr></table></figure>

<h4 id="IN操作"><a href="#IN操作" class="headerlink" title="IN操作"></a>IN操作</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_name, prod_price </span><br><span class="line">FROM products </span><br><span class="line">WHERE vend_id IN (1002, 1003);</span><br></pre></td></tr></table></figure>

<h4 id="NOT-操作符"><a href="#NOT-操作符" class="headerlink" title="NOT 操作符"></a>NOT 操作符</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_name, prod_price </span><br><span class="line">FROM products </span><br><span class="line">WHERE vend_id NOT IN (1002, 1003);</span><br></pre></td></tr></table></figure>

<h4 id="用通配符过滤"><a href="#用通配符过滤" class="headerlink" title="用通配符过滤"></a>用通配符过滤</h4><h5 id="LIKE操作符"><a href="#LIKE操作符" class="headerlink" title="LIKE操作符"></a>LIKE操作符</h5><ul>
<li>百分号通配符 % 任意字符任意次数（包括0次）,区分大小写</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_name</span><br><span class="line">FROM products</span><br><span class="line">WHERE prod_name LIKE &apos;aa%&apos;;</span><br></pre></td></tr></table></figure>

<p><strong>虽然可以匹配任意字符，但是不能个匹配NULL</strong></p>
<ul>
<li>下划线(_)通配符 只匹配单个字符</li>
</ul>
<p><strong>技巧</strong></p>
<blockquote>
<p>不要过度使用通配符，在确实需要的时候使用</p>
<p>不要放在搜索模式的开始处</p>
</blockquote>
<h4 id="用正则表达式进行搜索"><a href="#用正则表达式进行搜索" class="headerlink" title="用正则表达式进行搜索"></a>用正则表达式进行搜索</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">SELECT prod_name</span><br><span class="line">FROM products</span><br><span class="line">WHERE prod_name REGEXP &apos;1000&apos;;  # 匹配包含内容 aaa1000可以被匹配到</span><br><span class="line"></span><br><span class="line">SELECT prod_name</span><br><span class="line">FROM products</span><br><span class="line">WHERE prod_name LIKE &apos;1000&apos;;  # 匹配整列数据内容 aaa1000无法匹配到</span><br></pre></td></tr></table></figure>

<h3 id="创建计算字段"><a href="#创建计算字段" class="headerlink" title="创建计算字段"></a>创建计算字段</h3><h4 id="拼接字段"><a href="#拼接字段" class="headerlink" title="拼接字段"></a>拼接字段</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"># Concat 拼接字符</span><br><span class="line"># RTrim 去除空格 </span><br><span class="line"># AS 起别名</span><br><span class="line">SELECT Concat(RTrim(vend_name), &apos; (&apos;, vend_country,&apos;)&apos;) AS  vend_title</span><br><span class="line">FROM vendors;</span><br></pre></td></tr></table></figure>

<h4 id="执行算数计算"><a href="#执行算数计算" class="headerlink" title="执行算数计算"></a>执行算数计算</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SELECT quantity*item_price AS expanded_price</span><br><span class="line">FROM orderitems;</span><br></pre></td></tr></table></figure>

<h3 id="使用数据处理函数"><a href="#使用数据处理函数" class="headerlink" title="使用数据处理函数"></a>使用数据处理函数</h3><p>不同的DBMS上，函数差异较大，所以可移植性差异较大。</p>
<h4 id="文本处理函数"><a href="#文本处理函数" class="headerlink" title="文本处理函数"></a>文本处理函数</h4><table>
<thead>
<tr>
<th>函数</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>Left</td>
<td>返回左边字符</td>
</tr>
<tr>
<td>Length</td>
<td>返回串的长度</td>
</tr>
<tr>
<td>Locate</td>
<td>找出串的一个子串</td>
</tr>
<tr>
<td>Lower</td>
<td>转换为小写</td>
</tr>
<tr>
<td>LTrim</td>
<td>去掉串左边的小写</td>
</tr>
<tr>
<td>Right</td>
<td>返回串右边的字符</td>
</tr>
<tr>
<td>RTrim</td>
<td>去掉串右边的空格</td>
</tr>
<tr>
<td>Soundex</td>
<td>返回串的SOUBNDEX的值</td>
</tr>
<tr>
<td>SubString</td>
<td>返回子串的字符</td>
</tr>
<tr>
<td>Upper</td>
<td>将串转换为大写</td>
</tr>
</tbody></table>
<h4 id="返回日期和时间处理函数"><a href="#返回日期和时间处理函数" class="headerlink" title="返回日期和时间处理函数"></a>返回日期和时间处理函数</h4><p><strong>应该总是用4位年份</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">#日期范围内的数据</span><br><span class="line">SELECT  cust_id</span><br><span class="line">FROM orders</span><br><span class="line">WHERE Date(order_date) BETWEEN &apos;2015-09-01&apos; AND &apos;2019-09-30&apos;</span><br></pre></td></tr></table></figure>

<h4 id="数值处理函数"><a href="#数值处理函数" class="headerlink" title="数值处理函数"></a>数值处理函数</h4><p>常用的 Abs() Mod() Rand()</p>
<h4 id="汇总数据"><a href="#汇总数据" class="headerlink" title="汇总数据"></a>汇总数据</h4><table>
<thead>
<tr>
<th>函数</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>AVG</td>
<td>返回平均值</td>
</tr>
<tr>
<td>COUNT</td>
<td>返回行数</td>
</tr>
<tr>
<td>MAX</td>
<td>返回最大值</td>
</tr>
<tr>
<td>MIN</td>
<td>返回最小值</td>
</tr>
<tr>
<td>SUM</td>
<td>返回和</td>
</tr>
</tbody></table>
<h5 id="COUNT函数"><a href="#COUNT函数" class="headerlink" title="COUNT函数"></a>COUNT函数</h5><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"># 返回某一列的个数，包括NULL</span><br><span class="line">SELECT COUNT(*) AS num_cust</span><br><span class="line">FROM customers;</span><br><span class="line"></span><br><span class="line"># 返回某一列的非空的个数</span><br><span class="line">SELECT COUNT(column) AS num_cust</span><br><span class="line">FROM customers;</span><br></pre></td></tr></table></figure>

<h5 id="MAX函数"><a href="#MAX函数" class="headerlink" title="MAX函数"></a>MAX函数</h5><p>返回一列最大值，或者日期，如果是文本，则数据按相应的列排序，返回最后一行。</p>
<p>MAX是忽略NULL的</p>
<h5 id="SUM函数"><a href="#SUM函数" class="headerlink" title="SUM函数"></a>SUM函数</h5><p>多列函数的求和</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT SUM(item_price*quantity) AS total_price</span><br><span class="line">FROM orderitems</span><br><span class="line">WHERE order_num = 20005;</span><br></pre></td></tr></table></figure>

<p>去重求平均</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT AVG(DISTINCT item_price) AS avg_price</span><br><span class="line">FROM products</span><br><span class="line">WHERE vend_id = 1003;</span><br></pre></td></tr></table></figure>

<h3 id="分组数据-GROUP-BY"><a href="#分组数据-GROUP-BY" class="headerlink" title="分组数据 GROUP BY"></a>分组数据 GROUP BY</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT vend_id, COUNT(*) AS num_prods</span><br><span class="line">FROM products</span><br><span class="line">GrOUP BY vend_id;</span><br></pre></td></tr></table></figure>

<ul>
<li>GROUP BY子句可以包含惹你数目的列</li>
<li>GROUP BY 子句中嵌套分组，数据将在最后规定的分组上进行汇总。</li>
<li>GROUP BY子句中的每一个列，必须是检索列或者有效的表达式。不能是聚集函数</li>
<li>除聚集外，SELECT语句中的每一个列都必须在GROUP BY子句给出</li>
<li>如果分组中具有NULL，NULL作为单独分组返回。</li>
<li>GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。</li>
</ul>
<h4 id="过滤分组-HAVING"><a href="#过滤分组-HAVING" class="headerlink" title="过滤分组  HAVING"></a>过滤分组  HAVING</h4><p>WHERE 用于过滤行，HAVING用于过滤组</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"># 所有客户订单数量大于2的信息</span><br><span class="line">SELECT cust_id, COUNT(*) AS orders</span><br><span class="line">FROM orders</span><br><span class="line">GROUP BY cust_id</span><br><span class="line">HAVING COUNT(*) &gt;= 2;</span><br></pre></td></tr></table></figure>

<p>同时使用WHERE 和HAVING</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"># 列出2个以上，价格10以上的产品的供应商</span><br><span class="line">SELECT vend_id, COUNT(*) AS num_prods</span><br><span class="line">FROM products</span><br><span class="line">WHERE prod_price &gt;= 10</span><br><span class="line">GROUP BY vend_id</span><br><span class="line">HAVING COUNT(*) &gt; 2;</span><br></pre></td></tr></table></figure>

<h4 id="分组和排序"><a href="#分组和排序" class="headerlink" title="分组和排序"></a>分组和排序</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"># 按照总计订单价格排序输出</span><br><span class="line">SELECT order_num ,SUM(quantity*item_price) AS ordertotal</span><br><span class="line">FROM orderitems</span><br><span class="line">GrOUP BY order_num</span><br><span class="line">HAVING SUM(quantity*item_price) &gt;= 50</span><br><span class="line">ORDER BY ordertotal;</span><br></pre></td></tr></table></figure>

<h4 id="SELECT子句顺序"><a href="#SELECT子句顺序" class="headerlink" title="SELECT子句顺序"></a>SELECT子句顺序</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT</span><br></pre></td></tr></table></figure>

<h3 id="使用子查询"><a href="#使用子查询" class="headerlink" title="使用子查询"></a>使用子查询</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SELECT cust_id</span><br><span class="line">FROM orders</span><br><span class="line">WHERE order_num IN (SELECT order_num</span><br><span class="line">										FROM orderitems</span><br><span class="line">										WHERE prod_id = &apos;TNT2&apos;);</span><br></pre></td></tr></table></figure>

<p>子查询并不是最有效的方法</p>
<h3 id="联结表"><a href="#联结表" class="headerlink" title="联结表"></a>联结表</h3><p>关系型数据库</p>
<h4 id="外键"><a href="#外键" class="headerlink" title="外键"></a>外键</h4><blockquote>
<p>关系型数据库中，某表中的一列是另外一个表的主键，则这列是外键。</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">SELECT vend_name,prod_name,prod_price</span><br><span class="line">FROM vendors, products</span><br><span class="line">WHERE vendors.vend_id = products.vend_id</span><br><span class="line">ORDER BY vend_name, prod_name;</span><br></pre></td></tr></table></figure>

<h4 id="笛卡尔积"><a href="#笛卡尔积" class="headerlink" title="笛卡尔积"></a>笛卡尔积</h4><blockquote>
<p>没有联结条件的表关系返回的结果为笛卡尔积。</p>
<p>检索出来行的数目将是第一个表的行乘以第二个表的行数。</p>
</blockquote>
<h4 id="内部联结-INNER-JOIN-…-ON"><a href="#内部联结-INNER-JOIN-…-ON" class="headerlink" title="内部联结 INNER JOIN … ON"></a>内部联结 INNER JOIN … ON</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT vend_name,prod_name,prod_price</span><br><span class="line">FROM vendors INNER JOIN products</span><br><span class="line">ON vendors,vend_id = products.vend_id;</span><br></pre></td></tr></table></figure>

<h4 id="自联结"><a href="#自联结" class="headerlink" title="自联结"></a>自联结</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"># 自己和自己联结</span><br><span class="line">SELECT p1.prod_id,p1.prod_name</span><br><span class="line">FROM products AS p1,products AS p2</span><br><span class="line">WHERE p1.vend_id = p2.vend_id</span><br><span class="line">AND p2.prod_id = &apos;DINTR&apos;;</span><br></pre></td></tr></table></figure>

<h4 id="自然联结"><a href="#自然联结" class="headerlink" title="自然联结"></a>自然联结</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">#排除多次出现，使每一列治返回一次</span><br><span class="line">SELECT b.*, a.order_name,a.order_date</span><br><span class="line">FROM customers AS b, orders AS a</span><br><span class="line">WHERE b.cust_id = a.cust_id;</span><br></pre></td></tr></table></figure>

<h4 id="外部联结-LEFT-RIGHT-OUTER-JOIN…ON"><a href="#外部联结-LEFT-RIGHT-OUTER-JOIN…ON" class="headerlink" title="外部联结 LEFT/RIGHT  OUTER JOIN…ON"></a>外部联结 LEFT/RIGHT  OUTER JOIN…ON</h4><p>包含两表不关联的行的联结方式，叫做外部联结。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT a.cust_id,b.order_num</span><br><span class="line">FROM customers AS a LEFT OUTER JOIN orders AS b</span><br><span class="line">ON a.cust_id = b.cust_id; # a的某些cust_id 在b中没有，a在左边，a显示全部，b没有的部分显示NULL</span><br></pre></td></tr></table></figure>

<h3 id="组合查询-UNION"><a href="#组合查询-UNION" class="headerlink" title="组合查询 UNION"></a>组合查询 UNION</h3><p>将多条SELECT语句的结果作为一次查询结果输出</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">SELECT a.cust_id,a.cust_name</span><br><span class="line">FROM customers AS a</span><br><span class="line">WHERE a.cust_id &lt; 10</span><br><span class="line">UNION</span><br><span class="line">SELECT a.cust_id,a.cust_name</span><br><span class="line">FROM customers AS a</span><br><span class="line">WHERE a.cust_id &gt;100;</span><br><span class="line"></span><br><span class="line">#等价于</span><br><span class="line">SELECT a.cust_id,a.cust_name</span><br><span class="line">FROM customers AS a</span><br><span class="line">WHERE a.cust_id &lt; 10 OR  a.cust_id &gt; 100;</span><br></pre></td></tr></table></figure>

<p><strong>自动去重</strong></p>
<blockquote>
<p>UNION 可以自动把重复的行数去除</p>
</blockquote>
<p>如果不需要去掉重复使用<strong>UNION ALL</strong></p>
<blockquote>
<p>UNION只能使用一次ORDER BY，且放在最后一个SELECT后面。</p>
</blockquote>
<h3 id="全文本搜索（因为不是所有引擎支持，所以忽略）"><a href="#全文本搜索（因为不是所有引擎支持，所以忽略）" class="headerlink" title="全文本搜索（因为不是所有引擎支持，所以忽略）"></a>全文本搜索（因为不是所有引擎支持，所以忽略）</h3><h3 id="插入数据"><a href="#插入数据" class="headerlink" title="插入数据"></a>插入数据</h3><h4 id="插入完整的行"><a href="#插入完整的行" class="headerlink" title="插入完整的行"></a>插入完整的行</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">INSERT INTO Customers</span><br><span class="line">VALUE (NULL,</span><br><span class="line">      &apos;XuQi&apos;,</span><br><span class="line">      &apos;123&apos;);</span><br></pre></td></tr></table></figure>

<p><strong>更安全的插入方法</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">INSERT INTO Customers (cust_name, /* 不需要指定cust_id */</span><br><span class="line">                      cust_code)</span><br><span class="line">                      VALUE(&apos;XuQi&apos;,&apos;123&apos;);</span><br></pre></td></tr></table></figure>

<h4 id="插入行的一部分"><a href="#插入行的一部分" class="headerlink" title="插入行的一部分"></a>插入行的一部分</h4><p>某些列可以被省略，但是这些列必须满足以下条件:</p>
<ul>
<li>该列定义为允许NULL值</li>
<li>在表定义中给出默认值。和表示如果不给出值，将使用默认值。</li>
</ul>
<h3 id="更新和删除数据"><a href="#更新和删除数据" class="headerlink" title="更新和删除数据"></a>更新和删除数据</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">UPDATE customers</span><br><span class="line">SET cust_email = &apos;xuqi.jx@gmail.com&apos;</span><br><span class="line">WHERE cust_id = 10005;</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">DELETE FROM customers</span><br><span class="line">WHERE cust_id = 10005;</span><br></pre></td></tr></table></figure>

<h3 id="创建和操纵表"><a href="#创建和操纵表" class="headerlink" title="创建和操纵表"></a>创建和操纵表</h3><h4 id="创建表"><a href="#创建表" class="headerlink" title="创建表"></a>创建表</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLES customers</span><br><span class="line">(</span><br><span class="line">  cust_id int NOT NULL AUTO_INCREEMENT,</span><br><span class="line">  cust_name char(50) NOT NULL DEFAULT &apos;custA&apos;,</span><br><span class="line">  cust_address char(50) NULL,</span><br><span class="line">  PRIMARY KEY(cust_id)</span><br><span class="line">) ENGINE=InnoDB;</span><br></pre></td></tr></table></figure>

<p><strong>数据库引擎</strong></p>
<p><strong>InnoDB</strong> 可靠的事务处理引擎，他不支持全文本搜索。</p>
<p><strong>MEMORY</strong>在与功能上等同于MyISAM，但由于数据存储在内存，所以速度快。</p>
<p><strong>MyISAM</strong>是一个性能极高的引擎，他支持全文本搜索，但不支持事务。</p>
<h4 id="修改表"><a href="#修改表" class="headerlink" title="修改表"></a>修改表</h4><p>常用于定义外键</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">ALTER TABLE orderitems</span><br><span class="line">ADD CONSTRAINT fk_orderitems_orders</span><br><span class="line">FOREIGN KEY (order_num) REFERENCES orders (order_num);</span><br></pre></td></tr></table></figure>

<h4 id="删除表"><a href="#删除表" class="headerlink" title="删除表"></a>删除表</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">DROP TABLES customers2;</span><br></pre></td></tr></table></figure>

<h4 id="重命名表"><a href="#重命名表" class="headerlink" title="重命名表"></a>重命名表</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">RENAME TABLE customers2 TO customers;</span><br></pre></td></tr></table></figure>

<h3 id="使用视图"><a href="#使用视图" class="headerlink" title="使用视图"></a>使用视图</h3><p>视图是虚拟的表</p>
<h4 id="为什么使用视图"><a href="#为什么使用视图" class="headerlink" title="为什么使用视图"></a>为什么使用视图</h4><ul>
<li><p>重用SQL语句</p>
</li>
<li><p>简化复杂的SQL操作</p>
</li>
<li><p>使用表的组成部分而不是整个表</p>
</li>
<li><p>保护数据</p>
</li>
<li><p>更改数据格式和表示</p>
</li>
</ul>
<h4 id="视图的规则和限制"><a href="#视图的规则和限制" class="headerlink" title="视图的规则和限制"></a>视图的规则和限制</h4><ul>
<li>与表一样，视图必须唯一命名</li>
<li>对于可以穿件的视图数目咩有限制</li>
<li>为了创建视图，必须有足够的权限</li>
<li>视图可以嵌套</li>
<li>ORDER BY可以被用于视图</li>
<li>视图不能索引，不能由关联的触发器和默认值</li>
<li>视图可以和表一起使用</li>
</ul>
<h4 id="使用视图-1"><a href="#使用视图-1" class="headerlink" title="使用视图"></a>使用视图</h4><ul>
<li><p>视图用CREATE VIEW 语句创建</p>
</li>
<li><p>使用SHOW CREATE VIEW viewname来查看创建视图的语句</p>
</li>
<li><p>使用DROP删除视图</p>
</li>
<li><p>更新视图时，先用DROP再用CREATE，也可以直接使用CREATE OR REPLACE VIEW</p>
</li>
</ul>
<h5 id="用视图简化复杂的联结"><a href="#用视图简化复杂的联结" class="headerlink" title="用视图简化复杂的联结"></a>用视图简化复杂的联结</h5><h5 id="用视图重新格式化检索出的数据"><a href="#用视图重新格式化检索出的数据" class="headerlink" title="用视图重新格式化检索出的数据"></a>用视图重新格式化检索出的数据</h5><h5 id="用视图过滤不想要的数据"><a href="#用视图过滤不想要的数据" class="headerlink" title="用视图过滤不想要的数据"></a>用视图过滤不想要的数据</h5><h3 id="使用存储过程"><a href="#使用存储过程" class="headerlink" title="使用存储过程"></a>使用存储过程</h3><p>批处理保存一条或多条MYSQL语句的集合</p>
<h3 id="使用游标"><a href="#使用游标" class="headerlink" title="使用游标"></a>使用游标</h3><h3 id="使用触发器"><a href="#使用触发器" class="headerlink" title="使用触发器"></a>使用触发器</h3><h3 id="管理事务处理"><a href="#管理事务处理" class="headerlink" title="管理事务处理"></a>管理事务处理</h3><h3 id="全球化和本地化"><a href="#全球化和本地化" class="headerlink" title="全球化和本地化"></a>全球化和本地化</h3><h3 id="安全管理"><a href="#安全管理" class="headerlink" title="安全管理"></a>安全管理</h3><h3 id="数据库维护"><a href="#数据库维护" class="headerlink" title="数据库维护"></a>数据库维护</h3><h3 id="改善性能"><a href="#改善性能" class="headerlink" title="改善性能"></a>改善性能</h3>
    </div>

    
    
    
        
      

      <footer class="post-footer">
          
            
          
          <div class="post-tags">
            
              <a href="/tags/MySQL数据库/" rel="tag"># MySQL数据库</a>
            
          </div>
        

        

          <div class="post-nav">
            <div class="post-nav-next post-nav-item">
              
                <a href="/2019/06/25/C++性能优化指南/" rel="next" title="C++性能优化指南">
                  <i class="fa fa-chevron-left"></i> C++性能优化指南
                </a>
              
            </div>

            <span class="post-nav-divider"></span>

            <div class="post-nav-prev post-nav-item">
              
                <a href="/2019/07/01/C++常用工具类/" rel="prev" title="C++常用工具类">
                  C++常用工具类 <i class="fa fa-chevron-right"></i>
                </a>
              
            </div>
          </div>
        
      </footer>
    
  </div>
  
  
  
  </article>

  </div>


          </div>
          

        </div>
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    <div class="sidebar-inner">
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap">
            文章目录
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview-wrap">
            站点概览
          </li>
        </ul>
      

      <div class="site-overview-wrap sidebar-panel">
        <div class="site-overview">

          <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
  <p class="site-author-name" itemprop="name">XuQi</p>
  <div class="site-description motion-element" itemprop="description"></div>
</div>
  <nav class="site-state motion-element">
      <div class="site-state-item site-state-posts">
        
          <a href="/archives/">
        
          <span class="site-state-item-count">44</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
    
      
      
      <div class="site-state-item site-state-tags">
        
        
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
          
        
        <span class="site-state-item-count">30</span>
        <span class="site-state-item-name">标签</span>
        
      </div>
    
  </nav>



        </div>
      </div>
      <!--noindex-->
        <div class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc">

            
            
            
            

            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-3"><a class="nav-link" href="#数据库连接"><span class="nav-number">1.</span> <span class="nav-text">数据库连接</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#开启MySQL的远程帐号"><span class="nav-number">1.1.</span> <span class="nav-text">开启MySQL的远程帐号</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#MySQL修改密码"><span class="nav-number">1.2.</span> <span class="nav-text">MySQL修改密码</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#创建数据库"><span class="nav-number">2.</span> <span class="nav-text">创建数据库</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#创建数据库表"><span class="nav-number">3.</span> <span class="nav-text">创建数据库表</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#检索数据"><span class="nav-number">4.</span> <span class="nav-text">检索数据</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#排序检索数据"><span class="nav-number">4.1.</span> <span class="nav-text">排序检索数据</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#过滤数据"><span class="nav-number">5.</span> <span class="nav-text">过滤数据</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#使用WHERE子句"><span class="nav-number">5.1.</span> <span class="nav-text">使用WHERE子句</span></a><ol class="nav-child"><li class="nav-item nav-level-5"><a class="nav-link" href="#计算次序"><span class="nav-number">5.1.1.</span> <span class="nav-text">计算次序</span></a></li></ol></li><li class="nav-item nav-level-4"><a class="nav-link" href="#IN操作"><span class="nav-number">5.2.</span> <span class="nav-text">IN操作</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#NOT-操作符"><span class="nav-number">5.3.</span> <span class="nav-text">NOT 操作符</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#用通配符过滤"><span class="nav-number">5.4.</span> <span class="nav-text">用通配符过滤</span></a><ol class="nav-child"><li class="nav-item nav-level-5"><a class="nav-link" href="#LIKE操作符"><span class="nav-number">5.4.1.</span> <span class="nav-text">LIKE操作符</span></a></li></ol></li><li class="nav-item nav-level-4"><a class="nav-link" href="#用正则表达式进行搜索"><span class="nav-number">5.5.</span> <span class="nav-text">用正则表达式进行搜索</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#创建计算字段"><span class="nav-number">6.</span> <span class="nav-text">创建计算字段</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#拼接字段"><span class="nav-number">6.1.</span> <span class="nav-text">拼接字段</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#执行算数计算"><span class="nav-number">6.2.</span> <span class="nav-text">执行算数计算</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#使用数据处理函数"><span class="nav-number">7.</span> <span class="nav-text">使用数据处理函数</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#文本处理函数"><span class="nav-number">7.1.</span> <span class="nav-text">文本处理函数</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#返回日期和时间处理函数"><span class="nav-number">7.2.</span> <span class="nav-text">返回日期和时间处理函数</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#数值处理函数"><span class="nav-number">7.3.</span> <span class="nav-text">数值处理函数</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#汇总数据"><span class="nav-number">7.4.</span> <span class="nav-text">汇总数据</span></a><ol class="nav-child"><li class="nav-item nav-level-5"><a class="nav-link" href="#COUNT函数"><span class="nav-number">7.4.1.</span> <span class="nav-text">COUNT函数</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#MAX函数"><span class="nav-number">7.4.2.</span> <span class="nav-text">MAX函数</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#SUM函数"><span class="nav-number">7.4.3.</span> <span class="nav-text">SUM函数</span></a></li></ol></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#分组数据-GROUP-BY"><span class="nav-number">8.</span> <span class="nav-text">分组数据 GROUP BY</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#过滤分组-HAVING"><span class="nav-number">8.1.</span> <span class="nav-text">过滤分组  HAVING</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#分组和排序"><span class="nav-number">8.2.</span> <span class="nav-text">分组和排序</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#SELECT子句顺序"><span class="nav-number">8.3.</span> <span class="nav-text">SELECT子句顺序</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#使用子查询"><span class="nav-number">9.</span> <span class="nav-text">使用子查询</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#联结表"><span class="nav-number">10.</span> <span class="nav-text">联结表</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#外键"><span class="nav-number">10.1.</span> <span class="nav-text">外键</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#笛卡尔积"><span class="nav-number">10.2.</span> <span class="nav-text">笛卡尔积</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#内部联结-INNER-JOIN-…-ON"><span class="nav-number">10.3.</span> <span class="nav-text">内部联结 INNER JOIN … ON</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#自联结"><span class="nav-number">10.4.</span> <span class="nav-text">自联结</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#自然联结"><span class="nav-number">10.5.</span> <span class="nav-text">自然联结</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#外部联结-LEFT-RIGHT-OUTER-JOIN…ON"><span class="nav-number">10.6.</span> <span class="nav-text">外部联结 LEFT/RIGHT  OUTER JOIN…ON</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#组合查询-UNION"><span class="nav-number">11.</span> <span class="nav-text">组合查询 UNION</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#全文本搜索（因为不是所有引擎支持，所以忽略）"><span class="nav-number">12.</span> <span class="nav-text">全文本搜索（因为不是所有引擎支持，所以忽略）</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#插入数据"><span class="nav-number">13.</span> <span class="nav-text">插入数据</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#插入完整的行"><span class="nav-number">13.1.</span> <span class="nav-text">插入完整的行</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#插入行的一部分"><span class="nav-number">13.2.</span> <span class="nav-text">插入行的一部分</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#更新和删除数据"><span class="nav-number">14.</span> <span class="nav-text">更新和删除数据</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#创建和操纵表"><span class="nav-number">15.</span> <span class="nav-text">创建和操纵表</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#创建表"><span class="nav-number">15.1.</span> <span class="nav-text">创建表</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#修改表"><span class="nav-number">15.2.</span> <span class="nav-text">修改表</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#删除表"><span class="nav-number">15.3.</span> <span class="nav-text">删除表</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#重命名表"><span class="nav-number">15.4.</span> <span class="nav-text">重命名表</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#使用视图"><span class="nav-number">16.</span> <span class="nav-text">使用视图</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#为什么使用视图"><span class="nav-number">16.1.</span> <span class="nav-text">为什么使用视图</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#视图的规则和限制"><span class="nav-number">16.2.</span> <span class="nav-text">视图的规则和限制</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#使用视图-1"><span class="nav-number">16.3.</span> <span class="nav-text">使用视图</span></a><ol class="nav-child"><li class="nav-item nav-level-5"><a class="nav-link" href="#用视图简化复杂的联结"><span class="nav-number">16.3.1.</span> <span class="nav-text">用视图简化复杂的联结</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#用视图重新格式化检索出的数据"><span class="nav-number">16.3.2.</span> <span class="nav-text">用视图重新格式化检索出的数据</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#用视图过滤不想要的数据"><span class="nav-number">16.3.3.</span> <span class="nav-text">用视图过滤不想要的数据</span></a></li></ol></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#使用存储过程"><span class="nav-number">17.</span> <span class="nav-text">使用存储过程</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#使用游标"><span class="nav-number">18.</span> <span class="nav-text">使用游标</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#使用触发器"><span class="nav-number">19.</span> <span class="nav-text">使用触发器</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#管理事务处理"><span class="nav-number">20.</span> <span class="nav-text">管理事务处理</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#全球化和本地化"><span class="nav-number">21.</span> <span class="nav-text">全球化和本地化</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#安全管理"><span class="nav-number">22.</span> <span class="nav-text">安全管理</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#数据库维护"><span class="nav-number">23.</span> <span class="nav-text">数据库维护</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#改善性能"><span class="nav-number">24.</span> <span class="nav-text">改善性能</span></a></li></ol></div>
            

          </div>
        </div>
      <!--/noindex-->
      

    </div>
  </aside>
  <div id="sidebar-dimmer"></div>


      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright">&copy; <span itemprop="copyrightYear">2019</span>
  <span class="with-love" id="animate">
    <i class="fa fa-user"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">XuQi</span>
</div>
  <div class="powered-by">由 <a href="https://hexo.io" class="theme-link" rel="noopener" target="_blank">Hexo</a> 强力驱动 v3.9.0</div>
  <span class="post-meta-divider">|</span>
  <div class="theme-info">主题 – <a href="https://theme-next.org" class="theme-link" rel="noopener" target="_blank">NexT.Muse</a> v7.3.0</div>

        








        
      </div>
    </footer>
      <div class="back-to-top">
        <i class="fa fa-arrow-up"></i>
      </div>

    

  </div>

  
  <script src="/lib/jquery/index.js?v=3.4.1"></script>
  <script src="/lib/velocity/velocity.min.js?v=1.2.1"></script>
  <script src="/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>

<script src="/js/utils.js?v=7.3.0"></script>
  <script src="/js/motion.js?v=7.3.0"></script>


  <script src="/js/schemes/muse.js?v=7.3.0"></script>


<script src="/js/next-boot.js?v=7.3.0"></script>




  




























  

  

  


  
  <script src="/js/scrollspy.js?v=7.3.0"></script>
<script src="/js/post-details.js?v=7.3.0"></script>


</body>
</html>
